/* $RCSFile: install.sql $
 Apache license v2 2011 by Christian Surieux
 Removing this comment is forbiddden
 $Source: ?/AddonNice.ImageManager/PageModules/CRMImage/install.sql $
 $log$
 Revision 38 2010/08/09 12:28:18  christian.surieux
   Adding Log comment to sql files
*/

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[adn_UpdateCRMImage]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[adn_UpdateCRMImage]
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[adn_UpdateCRMImageData]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[adn_UpdateCRMImageData]
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[adn_AddCRMImage]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[adn_AddCRMImage]
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[adn_DeleteCRMImage]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[adn_DeleteCRMImage]
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[adn_GetCRMImage]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[adn_GetCRMImage]
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[adn_GetCRMImageByEntityId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[adn_GetCRMImageByEntityId]
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[adn_GetAllCRMImages]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[adn_GetAllCRMImages]
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[adn_GetAllCRMImagesPaged]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[adn_GetAllCRMImagesPaged]
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[adn_GetAllCRMImagesPagedEx]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[adn_GetAllCRMImagesPagedEx]
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[adn_GetCRMImageFull]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[adn_GetCRMImageFull]
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[adn_GetCRMImagePic]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[adn_GetCRMImagePic]
GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[adn_CRMImage2]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[adn_CRMImage2] (
	[ImageId] [int] IDENTITY (1, 1) NOT NULL ,
	[CrmImageId] [uniqueidentifier] NULL ,
	[ImageLibId] [int] NOT NULL ,
	[Caption] [nvarchar] (256) NULL ,
	[Category] [int] not NULL ,
	[Keywords] [nvarchar] (256) NULL ,
	[CreatedByUser] [nvarchar] (100) NULL ,
	[CreatedDate] [datetime] not NULL ,
	[UpdatedByUser] [nvarchar] (100) NULL ,
	[UpdateDate] [datetime] not NULL ,
	[ShortDescription] [nvarchar] (512) NULL ,
	[MetadataXml] [xml] NULL ,
	[ImagePic1] [varbinary](max) NULL ,
	[ImagePic2] [varbinary](max) NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

ALTER TABLE [dbo].[adn_CRMImage2] ADD 
	CONSTRAINT [PK_adn_CRMImage2] PRIMARY KEY  NONCLUSTERED 
	(
		[ImageId]
	)  ON [PRIMARY] 

CREATE  UNIQUE  INDEX [IX_adn_CRMImage2] ON [dbo].[adn_CRMImage2]([CrmImageId]) ON [PRIMARY]
END
GO



CREATE PROCEDURE [dbo].[adn_UpdateCRMImage]
	(@ImageId 	[int ],
	@ImageLibId int,
	@CrmImageId [uniqueidentifier ],
	@Caption [nvarchar] (256),
	@Category [int],
	@Keywords nvarchar(256),
	@UpdatedByUser nvarchar(100),
	@ShortDescription nvarchar(512),
	@MetadataXml [xml],
	@ImagePic1 varbinary(max),
	@ImagePic2 varbinary(max)
)
AS 
UPDATE [adn_CRMImage2]
SET  
		[ImageLibId]		=	@ImageLibId,
		[CrmImageId] 		=	@CrmImageId,
		[Caption]			=	@Caption,
		[Category]			=	@Category,
		[Keywords]			=	@Keywords,
		[ShortDescription]	=	@ShortDescription,
		[UpdatedByUser]		=	@UpdatedByUser,
		[UpdateDate]		=	getutcdate(),
		[MetadataXml]		=	@MetadataXml,
		[ImagePic1]			=	@ImagePic1,
		[ImagePic2]			=	@ImagePic2
WHERE [ImageId]	 = @ImageId
GO

CREATE PROCEDURE [dbo].[adn_UpdateCRMImageData]
	(@ImageId 	[int ],
	@Caption [nvarchar] (256),
	@Category [int],
	@Keywords nvarchar(256),
	@UpdatedByUser nvarchar(100),
	@ShortDescription nvarchar(512),
	@MetadataXml [xml]
)
AS 
UPDATE [adn_CRMImage2]
SET  
		[Caption]			=	@Caption,
		[Category]			=	@Category,
		[Keywords]			=	@Keywords,
		[UpdatedByUser]		=	@UpdatedByUser,
		[UpdateDate]		=	getutcdate(),
		[ShortDescription]	=	@ShortDescription,
		[MetadataXml]		=	@MetadataXml
WHERE [ImageId]	 = @ImageId
GO



CREATE PROCEDURE [dbo].[adn_AddCRMImage]
(	@ImageId 	[int ] OUTPUT,
	@ImageLibId [int],
	@CrmImageId 	[uniqueidentifier ],
	@Caption [nvarchar] (256),
	@Category [int],
	@Keywords nvarchar(256),
	@CreatedByUser nvarchar(100),
	@CreatedDate datetime,
	@ShortDescription nvarchar(512),
	@MetadataXml [xml],
	@ImagePic1 varbinary(max),
	@ImagePic2 varbinary(max)
)
AS 
INSERT INTO [adn_CRMImage2]
	(
	[ImageLibId],
	[CrmImageId],
	[Caption],
	[Category],
	[Keywords],
	[CreatedByUser],
	[CreatedDate],
	[UpdatedByUser],
	[UpdateDate],
	[ShortDescription],
	[MetadataXml],
	[ImagePic1],			
	[ImagePic2]			
) 
VALUES 
	(
	@ImageLibId,
	@CrmImageId,
	@Caption,
	@Category,
	@Keywords,
	@CreatedByUser,
	@CreatedDate,
	@CreatedByUser,
	getutcdate(),
	@ShortDescription,
	@MetadataXml,
	@ImagePic1,
	@ImagePic2
)
SELECT @ImageId = @@IDENTITY
GO



CREATE PROCEDURE [dbo].[adn_DeleteCRMImage]
	(@ImageId 	[int])
AS DELETE FROM [adn_CRMImage2] WHERE [ImageId] = @ImageId
GO

CREATE PROCEDURE [dbo].[adn_GetCRMImage]
(	@ImageId int)
AS
if ( @ImageId = -1 )
	SELECT Top 1
		ImageId, 
		ImageLibId,
		CrmImageId,
		[Caption],
		[Category],
		[Keywords],
		[CreatedByUser],
		[CreatedDate],
		[UpdatedByUser],
		[UpdateDate],
		ShortDescription, 
		MetadataXml 
	FROM adn_CRMImage2 	
	ELSE
	SELECT ImageId, 
		ImageLibId,
		CrmImageId,
		[Caption],
		[Category],
		[Keywords],
		[CreatedByUser],
		[CreatedDate],
		[UpdatedByUser],
		[UpdateDate],
		ShortDescription, 
		MetadataXml
	FROM adn_CRMImage2 WHERE ImageId = @ImageId
GO


CREATE PROCEDURE [dbo].[adn_GetCRMImageByEntityId]
(	@CrmImageId uniqueidentifier)
AS
	SELECT ImageId, 
		ImageLibId,
		CrmImageId,
		[Caption],
		[Category],
		[Keywords],
		[CreatedByUser],
		[CreatedDate],
		[UpdatedByUser],
		[UpdateDate],
		ShortDescription, 
		MetadataXml
	FROM adn_CRMImage2 WHERE CrmImageId = @CrmImageId
GO

CREATE PROCEDURE [dbo].[adn_GetAllCRMImages]
AS
	SELECT ImageId, 
		ImageLibId,
		CrmImageId,
		[Caption],
		[Category],
		[Keywords],
		[CreatedByUser],
		[CreatedDate],
		[UpdatedByUser],
		[UpdateDate],
		ShortDescription, 
		MetadataXml
	FROM adn_CRMImage2 	
GO

CREATE PROCEDURE [dbo].[adn_GetAllCRMImagesPaged]
(
	@Page int = 1,
	@ImageLibId int = 0,
	@RecordsPerPage int = 10
)
AS
BEGIN
-- Find out the first AND last record we want
	DECLARE @FirstRec int, @LastRec int

--Create a temporary table
	CREATE TABLE #TempItems
	(
		ID					int IDENTITY,
		[ImageId] [int], 
		[ImageLibId] [int],
		[CrmImageId] [uniqueidentifier ],
		[Caption] [nvarchar] (256) NULL ,
		[Category] [int] not NULL ,
		[Keywords] [nvarchar] (256) NULL ,
		[CreatedByUser] [nvarchar] (100) NULL ,
		[CreatedDate] [datetime] not NULL ,
		[UpdatedByUser] [nvarchar] (100) NULL ,
		[UpdateDate] [datetime] not NULL ,
		[ShortDescription] [nvarchar] (512) NULL ,
		[MetadataXml] [xml] NULL 
	)

-- We don't want to RETURN the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
	SET NOCOUNT ON

-- INSERT the rows FROM tblItems into the temp. table
	INSERT INTO	#TempItems
		(
		[ImageId], 
		[ImageLibId],
		[CrmImageId],
		[Caption],
		[Category],
		[Keywords],
		[CreatedByUser],
		[CreatedDate],
		[UpdatedByUser],
		[UpdateDate],
		[ShortDescription],
		[MetadataXml]
		)
		SELECT 
		[ImageId], 
		[ImageLibId],
		[CrmImageId],
		[Caption],
		[Category],
		[Keywords],
		[CreatedByUser],
		[CreatedDate],
		[UpdatedByUser],
		[UpdateDate],
		[ShortDescription],
		[MetadataXml]
		FROM adn_CRMImage2 where ImageLibId = 	@ImageLibId 	

	SELECT @FirstRec = (@Page - 1) * @RecordsPerPage
	SELECT @LastRec = (@Page * @RecordsPerPage + 1)
	
-- Now, RETURN the SET of paged records, plus, an indication of we
-- have more records or not!
	SELECT *, (SELECT COUNT(*) FROM #TempItems) RecordCount
	FROM #TempItems
	WHERE ID > @FirstRec AND ID < @LastRec	ORDER BY ID
	
-- Turn NOCOUNT back OFF
	SET NOCOUNT OFF
END
GO

CREATE PROCEDURE [dbo].[adn_GetAllCRMImagesPagedEx]
(
	@Page int = 1,
	@ImageLibId int = 0,
	@RecordsPerPage int = 10,
	@CatId int = -1,
	@SortKey nvarchar(50)
)
AS
BEGIN
-- Find out the first AND last record we want
	DECLARE @FirstRec int, @LastRec int

--Create a temporary table
	CREATE TABLE #TempItems
	(
		ID	int IDENTITY,
		[ImageId] [int], 
		[ImageLibId] [int],
		[CrmImageId] [uniqueidentifier ],
		[Caption] [nvarchar] (256) NULL ,
		[Category] [int] not NULL ,
		[Keywords] [nvarchar] (256) NULL ,
		[CreatedByUser] [nvarchar] (100) NULL ,
		[CreatedDate] [datetime] not NULL ,
		[UpdatedByUser] [nvarchar] (100) NULL ,
		[UpdateDate] [datetime] not NULL ,
		[ShortDescription] [nvarchar] (512) NULL ,
		[MetadataXml] [xml] NULL 
	)

-- We don't want to RETURN the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
	SET NOCOUNT ON

-- INSERT the rows FROM tblItems into the temp. table
	IF ( @CatId = -1 )
	BEGIN
	INSERT INTO	#TempItems
		(
		[ImageId], 
		[ImageLibId],
		[CrmImageId],
		[Caption],
		[Category],
		[Keywords],
		[CreatedByUser],
		[CreatedDate],
		[UpdatedByUser],
		[UpdateDate],
		[ShortDescription],
		[MetadataXml]
		)
		SELECT 
		[ImageId], 
		[ImageLibId],
		[CrmImageId],
		[Caption],
		[Category],
		[Keywords],
		[CreatedByUser],
		[CreatedDate],
		[UpdatedByUser],
		[UpdateDate],
		[ShortDescription],
		[MetadataXml]
		FROM adn_CRMImage2 WHERE 	ImageLibId = @ImageLibId
	END
	ELSE
	BEGIN
	INSERT INTO	#TempItems
		(
		[ImageId], 
		[ImageLibId],
		[CrmImageId],
		[Caption],
		[Category],
		[Keywords],
		[CreatedByUser],
		[CreatedDate],
		[UpdatedByUser],
		[UpdateDate],
		[ShortDescription],
		[MetadataXml]
		)
		SELECT 
		[ImageId], 
		[ImageLibId],
		[CrmImageId],
		[Caption],
		[Category],
		[Keywords],
		[CreatedByUser],
		[CreatedDate],
		[UpdatedByUser],
		[UpdateDate],
		[ShortDescription],
		[MetadataXml]
		FROM adn_CRMImage2 WHERE ( @ImageLibId = @ImageLibId ) AND ( Category = @CatId )
	END

	SELECT @FirstRec = (@Page - 1) * @RecordsPerPage
	SELECT @LastRec = (@Page * @RecordsPerPage + 1)
	
	DECLARE @SQLSTRING nvarchar(2000)
	SET @SQLSTRING = 'SELECT *, (SELECT COUNT(*) FROM #TempItems) RecordCount FROM #TempItems WHERE ID > '+ CAST(@FirstRec AS nvarchar) +
		' AND ID < '+ CAST(@LastRec AS nvarchar) +' ORDER BY '+	@sortKey

	EXEC (@SQLSTRING)
-- Turn NOCOUNT back OFF
	SET NOCOUNT OFF
END
GO

CREATE PROCEDURE [dbo].[adn_GetCRMImageFull]
(	@ImageId int)
AS
	SELECT 
	[ImageId], 
	[ImageLibId],
	[CrmImageId],
	[CrmImageId],
	[Caption],
	[Category],
	[Keywords],
	[CreatedByUser],
	[CreatedDate],
	[UpdatedByUser],
	[UpdateDate],
	[ShortDescription],
	[MetadataXml]
	[ImagePic1],
	[ImagePic2] 
	FROM adn_CRMImage2 WHERE ImageId = @ImageId
GO


CREATE PROCEDURE [dbo].[adn_GetCRMImagePic]
(
	@ImageId int)
AS
	SELECT 
		ImagePic1,
		ImagePic2
	FROM adn_CRMImage2 	WHERE 	ImageId = @ImageId
GO



